﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace yfxt
{
    public partial class cx_gdcsx : Form
    {
        public cx_gdcsx()
        {
            InitializeComponent();
        }
        string sql_gccx, sql_dccx, sql_sxcx;
        cx_gdcsx_DataSet ds = new cx_gdcsx_DataSet();
        static string SQL = "data source=localhost;database=yf;uid=sa;pwd=123;timeout=60";

        private void button3_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void cx_gdcsx_Load(object sender, EventArgs e)
        {
            comboBox1.Items.Add("西药");
            comboBox1.Items.Add("中成药");
            comboBox1.Items.Add("中草药");
            try
            {
                sql_gccx = "select a.pydm,a.ypmc,a.yfgg,a.yfdw,sum(b.ypsl) as ypsl,a.yfgc from "+
                    "yk_typk a,"+
                    "yf_kcmx b where "+
                    "a.ypxh=b.ypxh and "+
                    "b.yfsb='" + mainform.yfsb + "' and "+
                    "a.yfzf=0 and "+
                    "a.ykzf=0 and "+
                    "a.yfgc<ypsl "+
                    " group by a.pydm,a.ypmc,a.yfgg,a.yfdw,a.yfgc "+
                    "order by a.pydm";

                sql_dccx = "select a.pydm,a.ypmc,a.yfgg,a.yfdw,sum(b.ypsl) as ypsl,a.yfdc from " +
                    "yk_typk a," +
                    "yf_kcmx b where " +
                    "a.ypxh=b.ypxh and " +
                    "b.yfsb='" + mainform.yfsb + "' and " +
                    "a.yfzf=0 and " +
                    "a.ykzf=0 and " +
                    "a.yfdc>ypsl "+
                    "group by a.pydm,a.ypmc,a.yfgg,a.yfdw,a.yfdc "+
                    "order by a.pydm";
                sql_sxcx = "select a.pydm,a.ypmc,a.yfgg,a.yfdw,b.ypph,b.ypxq from "+
                    "yk_typk a,"+
                    "yf_kcmx b where "+
                    "a.ypxh=b.ypxh and "+
                    "b.yfsb='" + mainform.yfsb + "' and "+
                    "a.yfzf=0 and "+
                    "a.ykzf=0 and "+
                    "b.ypxq<='"+dateTimePicker1.Value.ToShortDateString()+"' "+
                    "order by a.pydm";
                SqlConnection con = new SqlConnection(SQL);
                SqlDataAdapter da_gccx = new SqlDataAdapter(sql_gccx, con);
                da_gccx.Fill(ds.Tables["dt_gccx"]);
                cx_gccx_rpt rpt_gccx = new cx_gccx_rpt();
                rpt_gccx.SetDataSource(ds);
                crystalReportViewer1.ReportSource = rpt_gccx;

                SqlDataAdapter da_dccx = new SqlDataAdapter(sql_dccx, con);
                da_dccx.Fill(ds.Tables["dt_dccx"]);
                cx_dccx_rpt rpt_dccx = new cx_dccx_rpt();
                rpt_dccx.SetDataSource(ds);
                crystalReportViewer2.ReportSource = rpt_dccx;

                SqlDataAdapter da_sxcx = new SqlDataAdapter(sql_sxcx,con);
                da_sxcx.Fill(ds.Tables["dt_sxcx"]);
                cx_sxcx_rpt rpt_sxcx = new cx_sxcx_rpt();
                rpt_sxcx.SetDataSource(ds);
                crystalReportViewer3.ReportSource = rpt_sxcx;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (comboBox1.Text.Trim() == "西药")
            {
                ds.Clear();
                try
                {
                    sql_gccx = "select a.pydm,a.ypmc,a.yfgg,a.yfdw,sum(b.ypsl) as ypsl,a.yfgc from " +
                        "yk_typk a," +
                        "yf_kcmx b where " +
                        "a.ypxh=b.ypxh and " +
                        "b.yfsb='" + mainform.yfsb + "' and " +
                        "a.yfzf=0 and " +
                        "a.ykzf=0 and " +
                        "a.type=1 and "+
                        "a.yfgc<ypsl " +
                        " group by a.pydm,a.ypmc,a.yfgg,a.yfdw,a.yfgc " +
                        "order by a.pydm";

                    sql_dccx = "select a.pydm,a.ypmc,a.yfgg,a.yfdw,sum(b.ypsl) as ypsl,a.yfdc from " +
                        "yk_typk a," +
                        "yf_kcmx b where " +
                        "a.ypxh=b.ypxh and " +
                        "b.yfsb='" + mainform.yfsb + "' and " +
                        "a.yfzf=0 and " +
                        "a.ykzf=0 and " +
                        "a.type=1 and " +
                        "a.yfdc>ypsl " +
                        "group by a.pydm,a.ypmc,a.yfgg,a.yfdw,a.yfdc " +
                        "order by a.pydm";
                    sql_sxcx = "select a.pydm,a.ypmc,a.yfgg,a.yfdw,b.ypph,b.ypxq from " +
                        "yk_typk a," +
                        "yf_kcmx b where " +
                        "a.ypxh=b.ypxh and " +
                        "b.yfsb='" + mainform.yfsb + "' and " +
                        "a.yfzf=0 and " +
                        "a.ykzf=0 and " +
                        "a.type=1 and " +
                        "b.ypxq<='" + dateTimePicker1.Value.ToShortDateString() + "' " +
                        "order by a.pydm";
                    SqlConnection con = new SqlConnection(SQL);
                    SqlDataAdapter da_gccx = new SqlDataAdapter(sql_gccx, con);
                    da_gccx.Fill(ds.Tables["dt_gccx"]);
                    cx_gccx_rpt rpt_gccx = new cx_gccx_rpt();
                    rpt_gccx.SetDataSource(ds);
                    crystalReportViewer1.ReportSource = rpt_gccx;

                    SqlDataAdapter da_dccx = new SqlDataAdapter(sql_dccx, con);
                    da_dccx.Fill(ds.Tables["dt_dccx"]);
                    cx_dccx_rpt rpt_dccx = new cx_dccx_rpt();
                    rpt_dccx.SetDataSource(ds);
                    crystalReportViewer2.ReportSource = rpt_dccx;

                    SqlDataAdapter da_sxcx = new SqlDataAdapter(sql_sxcx, con);
                    da_sxcx.Fill(ds.Tables["dt_sxcx"]);
                    cx_sxcx_rpt rpt_sxcx = new cx_sxcx_rpt();
                    rpt_sxcx.SetDataSource(ds);
                    crystalReportViewer3.ReportSource = rpt_sxcx;
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
            }
            else if(comboBox1.Text.Trim() == "中成药")
            {
                ds.Clear();
                try
                {
                    sql_gccx = "select a.pydm,a.ypmc,a.yfgg,a.yfdw,sum(b.ypsl) as ypsl,a.yfgc from " +
                        "yk_typk a," +
                        "yf_kcmx b where " +
                        "a.ypxh=b.ypxh and " +
                        "b.yfsb='" + mainform.yfsb + "' and " +
                        "a.yfzf=0 and " +
                        "a.ykzf=0 and " +
                        "a.type=2 and " +
                        "a.yfgc<ypsl " +
                        " group by a.pydm,a.ypmc,a.yfgg,a.yfdw,a.yfgc " +
                        "order by a.pydm";

                    sql_dccx = "select a.pydm,a.ypmc,a.yfgg,a.yfdw,sum(b.ypsl) as ypsl,a.yfdc from " +
                        "yk_typk a," +
                        "yf_kcmx b where " +
                        "a.ypxh=b.ypxh and " +
                        "b.yfsb='" + mainform.yfsb + "' and " +
                        "a.yfzf=0 and " +
                        "a.ykzf=0 and " +
                        "a.type=2 and " +
                        "a.yfdc>ypsl " +
                        "group by a.pydm,a.ypmc,a.yfgg,a.yfdw,a.yfdc " +
                        "order by a.pydm";
                    sql_sxcx = "select a.pydm,a.ypmc,a.yfgg,a.yfdw,b.ypph,b.ypxq from " +
                        "yk_typk a," +
                        "yf_kcmx b where " +
                        "a.ypxh=b.ypxh and " +
                        "b.yfsb='" + mainform.yfsb + "' and " +
                        "a.yfzf=0 and " +
                        "a.ykzf=0 and " +
                        "a.type=2 and " +
                        "b.ypxq<='" + dateTimePicker1.Value.ToShortDateString() + "' " +
                        "order by a.pydm";
                    SqlConnection con = new SqlConnection(SQL);
                    SqlDataAdapter da_gccx = new SqlDataAdapter(sql_gccx, con);
                    da_gccx.Fill(ds.Tables["dt_gccx"]);
                    cx_gccx_rpt rpt_gccx = new cx_gccx_rpt();
                    rpt_gccx.SetDataSource(ds);
                    crystalReportViewer1.ReportSource = rpt_gccx;

                    SqlDataAdapter da_dccx = new SqlDataAdapter(sql_dccx, con);
                    da_dccx.Fill(ds.Tables["dt_dccx"]);
                    cx_dccx_rpt rpt_dccx = new cx_dccx_rpt();
                    rpt_dccx.SetDataSource(ds);
                    crystalReportViewer2.ReportSource = rpt_dccx;

                    SqlDataAdapter da_sxcx = new SqlDataAdapter(sql_sxcx, con);
                    da_sxcx.Fill(ds.Tables["dt_sxcx"]);
                    cx_sxcx_rpt rpt_sxcx = new cx_sxcx_rpt();
                    rpt_sxcx.SetDataSource(ds);
                    crystalReportViewer3.ReportSource = rpt_sxcx;
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
            }
            else if (comboBox1.Text.Trim() == "中草药")
            {
                ds.Clear();
                try
                {
                    sql_gccx = "select a.pydm,a.ypmc,a.yfgg,a.yfdw,sum(b.ypsl) as ypsl,a.yfgc from " +
                        "yk_typk a," +
                        "yf_kcmx b where " +
                        "a.ypxh=b.ypxh and " +
                        "b.yfsb='" + mainform.yfsb + "' and " +
                        "a.yfzf=0 and " +
                        "a.ykzf=0 and " +
                        "a.type=3 and " +
                        "a.yfgc<ypsl " +
                        " group by a.pydm,a.ypmc,a.yfgg,a.yfdw,a.yfgc " +
                        "order by a.pydm";

                    sql_dccx = "select a.pydm,a.ypmc,a.yfgg,a.yfdw,sum(b.ypsl) as ypsl,a.yfdc from " +
                        "yk_typk a," +
                        "yf_kcmx b where " +
                        "a.ypxh=b.ypxh and " +
                        "b.yfsb='" + mainform.yfsb + "' and " +
                        "a.yfzf=0 and " +
                        "a.ykzf=0 and " +
                        "a.type=3 and " +
                        "a.yfdc>ypsl " +
                        "group by a.pydm,a.ypmc,a.yfgg,a.yfdw,a.yfdc " +
                        "order by a.pydm";
                    sql_sxcx = "select a.pydm,a.ypmc,a.yfgg,a.yfdw,b.ypph,b.ypxq from " +
                        "yk_typk a," +
                        "yf_kcmx b where " +
                        "a.ypxh=b.ypxh and " +
                        "b.yfsb='" + mainform.yfsb + "' and " +
                        "a.yfzf=0 and " +
                        "a.ykzf=0 and " +
                        "a.type=3 and " +
                        "b.ypxq<='" + dateTimePicker1.Value.ToShortDateString() + "' " +
                        "order by a.pydm";
                    SqlConnection con = new SqlConnection(SQL);
                    SqlDataAdapter da_gccx = new SqlDataAdapter(sql_gccx, con);
                    da_gccx.Fill(ds.Tables["dt_gccx"]);
                    cx_gccx_rpt rpt_gccx = new cx_gccx_rpt();
                    rpt_gccx.SetDataSource(ds);
                    crystalReportViewer1.ReportSource = rpt_gccx;

                    SqlDataAdapter da_dccx = new SqlDataAdapter(sql_dccx, con);
                    da_dccx.Fill(ds.Tables["dt_dccx"]);
                    cx_dccx_rpt rpt_dccx = new cx_dccx_rpt();
                    rpt_dccx.SetDataSource(ds);
                    crystalReportViewer2.ReportSource = rpt_dccx;

                    SqlDataAdapter da_sxcx = new SqlDataAdapter(sql_sxcx, con);
                    da_sxcx.Fill(ds.Tables["dt_sxcx"]);
                    cx_sxcx_rpt rpt_sxcx = new cx_sxcx_rpt();
                    rpt_sxcx.SetDataSource(ds);
                    crystalReportViewer3.ReportSource = rpt_sxcx;
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
 
            }
        }


    }
}
